package com.nf.sms.dao.impl;

import com.nf.sms.dao.ProductDAo;
import com.nf.sms.entity.Product;
import com.nf.sms.utils.DbUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @AUTHOR CJ
 * <p>
 * *@DATE 2024 - 10 - 15
 */
public class ProductDaoImpl implements ProductDAo {


    @Override
    public List<Product> selectByPage(int currentPage, int pageCount, Product product) {
      //定义一个集合，用于存放查询数据
        List<Product> list = new ArrayList<>();

       //第一：定义要操作数据库的SQL语句，参数使用? 占位符代替
        StringBuilder sb = new StringBuilder();

        sb.append("select * from (");
        sb.append("select *, row_number() over(order by t1.product_id asc) as rownumber ");
        sb.append("from product as t1 ");
        sb.append("where 1=1 ");

        // 1. 定义 List 集合，用于存储查询条件 - Object是所有对象的父类
        List<Object> paramList = new ArrayList<>();

        // 2.动态拼接 SQL 语句
        if (product.getProductName() != null && !"".equals(product.getProductName().trim())) {
            sb.append("and product_name like ? ");
            paramList.add('%' + product.getProductName() + '%');
        }

        if (product.getProductType() != null && !"".equals(product.getProductType().trim())) {
            sb.append("and product_type=? ");
            paramList.add(product.getProductType());
        }
        sb.append(") as t2 ");
        sb.append("where rownumber between ? and ?");

        Connection conn = DbUtil.getConnection();
        PreparedStatement psmt = null;
        ResultSet rst = null;

        try {
            psmt = conn.prepareStatement(sb.toString());

            //语句对象。setXXX(参数索引,值)
            for (int i = 0; i < paramList.size(); i++) {
                psmt.setObject(i + 1,paramList.get(i));
            }
            psmt.setInt(paramList.size() +1,(currentPage - 1)*pageCount+1);
            psmt.setInt(paramList.size()+2,currentPage* pageCount);

            rst = psmt.executeQuery();

            while (rst.next())
            {
                String productId = rst.getString(1);
                String productName = rst.getString(2);
                String productType = rst.getString(3);
                float productPrice = rst.getFloat(4);
                int productCount = rst.getInt(5);
                String productImage = rst.getString(6);
                Date productDate = rst.getDate(7);
                String productDesc = rst.getString(8);
                int productSale = rst.getInt(9);
                int productStatus = rst.getInt(10);


                // 3.实例化实体对象 - 一个商品表示的就一行数据
                Product p = new Product();

                // 4.把各列数据封装到实体对象中
                p.setProductId(productId);
                p.setProductName(productName);
                p.setProductDate(productDate);
                p.setProductDesc(productDesc);
                p.setProductCount(productCount);
                p.setProductImage(productImage);
                p.setProductPrice(productPrice);
                p.setProductStatus(productStatus);
                p.setProductType(productType);
                p.setProductSale(productSale);

                // 5.把各行（商品）的数据添加到List集合中
                list.add(p);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(rst,psmt,conn);
        }

        return list;
    }

    @Override
    public long selectCount(Product product) {
        long r = 0;
        Connection conn = DbUtil.getConnection();
        StringBuilder sb = new StringBuilder("select count(product_id) from product where 1=1");

        List<Object> list = new ArrayList<>();
        if(product.getProductName()!=null && !"".equals(product.getProductName().trim())){
            sb.append("and product_name like ? ");
            list.add('%'+product.getProductName()+'%');
        }
        if(product.getProductType()!=null && !"".equals(product.getProductType().trim())){
            sb.append("and product_type=? ");
            list.add(product.getProductType());
        }


        PreparedStatement psmt = null;
        ResultSet rst =null;
        try {
            psmt = conn.prepareStatement(sb.toString());

            for(int i = 0;i < list.size();i++){
                psmt.setObject(i+1,list.get(i));

            }
            rst = psmt.executeQuery();

            if(rst.next()){
                r = rst.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DbUtil.close(rst,psmt,conn);
        }
        return r;
    }

    public static void main(String[] args) {
        ProductDaoImpl dao = new ProductDaoImpl();
        Product product = new Product();
        product.setProductName("");
        product.setProductType("家电");

        System.out.println(dao.selectCount(product));
    }
}
